﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;
using System.Data;
using System.Configuration;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_HRM_PS_EMP_BOOK_BANKS:IDAL_HRM_PS_EMP_BOOK_BANKS
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_HRM_PS_EMP_BOOK_BANKS> GetList(int? emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PS_EMP_BOOK_BANKS> infoList = new List<M_HRM_PS_EMP_BOOK_BANKS>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);

                strSQL = strSQL = "SELECT acc.book_seq as bank_acc_seq, concat(acc.bank_acc_no, ' : ', acc.bank_acc_name, '  ', b.bank_name) as bank_acc_name"
                    + " FROM hrm_ps_emp_book_banks acc"
                    //+ " LEFT JOIN fim_mas_bank_account_type_groups at ON acc.bank_acc_type_grp_seq = at.bank_acc_type_grp_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    //+ " LEFT JOIN bgm_mas_source s ON acc.bgm_source_seq = s.bgm_source_seq"
                    + " WHERE acc.record_status not like @delete_status"
                    + (emp_seq != null ?
                        " and acc.emp_seq = @emp_seq" : "")
                    //+ (bgm_source_seq != null ?
                        //" and acc.bgm_source_seq = @bgm_source_seq" : "")
                    + " ORDER BY bank_acc_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PS_EMP_BOOK_BANKS info = new M_HRM_PS_EMP_BOOK_BANKS();
                        info.BANK_ACC_SEQ = Convert.ToInt32(drc[i]["bank_acc_seq"]);
                        info.BANK_ACC_NAME = drc[i]["bank_acc_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
            //throw new NotImplementedException();
        }

        public IList<M_HRM_PS_EMP_BOOK_BANKS> GetFull(int? emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_HRM_PS_EMP_BOOK_BANKS> infoList = new List<M_HRM_PS_EMP_BOOK_BANKS>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);

                strSQL = strSQL = "SELECT acc.*, bb.branch_id, bb.branch_name, b.bank_id, b.bank_name"
                    + ", concat(np.prefix_short, emp.first_name, ' ', emp.last_name) as emp_name"
                    + " FROM hrm_ps_emp_book_banks acc"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON acc.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " WHERE acc.record_status not like @delete_status"
                    + (emp_seq != null ?
                        " and acc.emp_seq = @emp_seq" : "")
                    //+ (bgm_source_seq != null ?
                    //" and acc.bgm_source_seq = @bgm_source_seq" : "")
                    + " ORDER BY bank_acc_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_HRM_PS_EMP_BOOK_BANKS info = new M_HRM_PS_EMP_BOOK_BANKS();
                        info.BOOK_SEQ = Convert.ToInt32(drc[i]["BOOK_SEQ"]);
                        info.BANK_ACC_NO = drc[i]["bank_acc_no"].ToString();
                        info.BANK_ACC_NAME = drc[i]["emp_name"].ToString();
                        info.BRANCH_CODE = drc[i]["branch_id"].ToString();
                        info.BRANCH_NAME = drc[i]["branch_name"].ToString();
                        info.BANK_CODE = drc[i]["BANK_id"].ToString();
                        info.BANK_NAME = drc[i]["bank_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
            //throw new NotImplementedException();
        }
    }
}
